package org.keshe.user;

import org.keshe.data.BedData;
import org.keshe.data.StuData;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AdminUser extends User {
    protected AdminUser(String userName) {
        super(userName);
    }

    @Override
    public boolean changePasswd(String oldVal, String newVal) throws SQLException {
        // 检查旧密码
        String sql = "select * from Admin where username = ? and passwd = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, this.userName);
        preparedStatement.setString(2, oldVal);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next())
            return false;
        // 设置新密码
        sql = "update Admin set passwd = ? where username = ?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, newVal);
        preparedStatement.setString(2, this.userName);
        try {
            preparedStatement.execute();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            preparedStatement.close();
        }
        return true;
    }

    /**
     * 登录
     *
     * @param userName 用户名
     * @param passWd   密码
     * @return 若成功登录返回实例，否则返回NULL
     * @throws SQLException         数据库连接异常
     * @throws NullPointerException conn未初始化
     */
    public static AdminUser logOn(String userName, String passWd) throws SQLException, NullPointerException {
        String sql = "select * from Admin where username = ? and passwd = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, userName);
        preparedStatement.setString(2, passWd);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            preparedStatement.close();
            return new AdminUser(userName);
        }
        preparedStatement.close();
        return null;
    }
    /**
     * 获取所有学生的信息
     * @return 所有学生的信息
     * */
    public List<StuData> getAllStuData() throws SQLException {
        String sql = "select Student.*, BedName " +
                "from Student left join Studentbed on Student.s_id = Studentbed.s_id " +
                "left join BedNameTrans on Studentbed.bed_id = BedNameTrans.bed_id;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        return queryStuData(preparedStatement);
    }
    /**
     * 获取学生信息（无床位）
     * @param s_id 学号
     * @exception SQLException 查无此人
     * @return 学生信息
     * */
    public StuData getStuDataById(String s_id) throws SQLException {
        String sql = "select * from Student where s_id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, s_id);
        ResultSet rs = preparedStatement.executeQuery();
        StuData stuData;
        if(rs.next()){
            stuData = new StuData(rs.getString("s_id"), rs.getString("sname"),
                    rs.getString("sex"), rs.getString("major"));
        }else {
            throw new SQLException("查无此人！");
        }
        preparedStatement.close();
        return stuData;

    }

    /**
     * 添加一个学生
     * @param stuData 学生信息
     * @exception SQLException 添加失败
     * */
    public void addStuData(StuData stuData) throws SQLException{
        String sql = "insert into Student(s_id, sname, sex, major) value(?, ?, ?, ?);";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, stuData.getS_id());
        preparedStatement.setString(2, stuData.getSname());
        preparedStatement.setString(3, stuData.getSex());
        preparedStatement.setString(4, stuData.getMajor());
        try {
            preparedStatement.execute();
            conn.commit();
        }catch (SQLException e){
            conn.rollback();
            throw e;
        }finally {
            preparedStatement.close();
        }

    }

    /**
     * 修改学生信息
     * @param stuData 学生信息
     * @return 表中是否有这个学生
     * @throws SQLException 应该不会更新不成功
     */
    public boolean updateStuData(StuData stuData) throws SQLException{
        String sql = "select * from Student where s_id = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, stuData.getS_id());
        ResultSet rs = preparedStatement.executeQuery();
        if(!rs.next())
            return false;
        preparedStatement.close();
        String sql2 = "update Student\n" +
                "set sname = ? , sex = ? , major = ?\n" +
                "where s_id = ?;";
        preparedStatement = conn.prepareStatement(sql2);
        preparedStatement.setString(1, stuData.getSname());
        preparedStatement.setString(2, stuData.getSex());
        preparedStatement.setString(3, stuData.getMajor());
        preparedStatement.setString(4, stuData.getS_id());
        try {
            preparedStatement.execute();
            conn.commit();
        } catch (SQLException e){
            conn.rollback();
            throw e;
        }finally {
            preparedStatement.close();
        }
        return true;
    }
    public boolean deleteStuDataById(String s_id) throws SQLException {
        String sql = "select * from Student where s_id = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, s_id);
        ResultSet rs = preparedStatement.executeQuery();
        if(!rs.next())
            return false;
        preparedStatement.close();
        String sql2 = "delete from Student where s_id = ?";
        preparedStatement = conn.prepareStatement(sql2);
        preparedStatement.setString(1, s_id);
        try {
            preparedStatement.execute();
            conn.commit();
        } catch (SQLException e){
            conn.rollback();
            throw e;
        }finally {
            preparedStatement.close();
        }
        return true;
    }

    /**
     *  以关键字搜索学生信息
     * @param keyword 关键字
     * @return 学生信息
     * @exception SQLException 其他错误
     * */
    public List<StuData> searchStuData (String keyword) throws SQLException {
        String sql = "select Student.*, BedName " +
                "from Student left join Studentbed on Student.s_id = Studentbed.s_id " +
                "left join BedNameTrans on Studentbed.bed_id = BedNameTrans.bed_id " +
                "where Student.s_id = ? or sname = ? or sex = ? or major = ? or BedName = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, keyword);
        preparedStatement.setString(2, keyword);
        preparedStatement.setString(3, keyword);
        preparedStatement.setString(4, keyword);
        preparedStatement.setString(5, keyword);

        return queryStuData(preparedStatement);
    }

    /**
     *  以关键字模糊搜索学生信息
     * @param keyword 关键字
     * @return 学生信息
     * @exception SQLException 其他错误
     * */
    public List<StuData> searchStuDataFuzzy (String keyword) throws SQLException {
        String sql = "select Student.*, BedName " +
                "from Student left join Studentbed on Student.s_id = Studentbed.s_id " +
                "left join BedNameTrans on Studentbed.bed_id = BedNameTrans.bed_id " +
                "where Student.s_id like ? or sname like ? or sex like ? or major like ? or BedName like ?;";
        keyword = "%" + keyword + "%";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, keyword);
        preparedStatement.setString(2, keyword);
        preparedStatement.setString(3, keyword);
        preparedStatement.setString(4, keyword);
        preparedStatement.setString(5, keyword);

        return queryStuData(preparedStatement);
    }
    /**
     * 查询某位学生是否已有床位
     * @param s_id 学生信息
     * @return 结果
     * */
    public boolean isOccupyBed(String s_id) throws SQLException {
        String sql = "select s_id from Studentbed where s_id = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, s_id);
        ResultSet rs = preparedStatement.executeQuery();
        return rs.next();
    }

    /**
     * 为一名同学找新床位
     * @param stuData 学生信息
     * @return 结果
     * */
    public List<BedData> searchSpareBedForStu (StuData stuData) throws SQLException {
        String sql = "select BedName, DormName, BedNameTrans.floor,bname from BedNameTrans, DormNameTrans, Building\n" +
                "where BedNameTrans.d_id = DormNameTrans.d_id\n" +
                "and BedNameTrans.b_id = Building.b_id and Building.kind = ?\n" +
                "and BedNameTrans.bed_id not in (select Studentbed.bed_id from Studentbed);";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, stuData.getSex());
        ResultSet rs = preparedStatement.executeQuery();
        List<BedData> res = new ArrayList<>();
        while (rs.next()){
            res.add(new BedData(rs.getString("BedName"), rs.getString("DormName"),
                    rs.getString("floor"), rs.getString("bname")));
        }
        preparedStatement.close();
        return res;
    }

    /**
     * 学生入住
     * @param BedName 床位号
     * @param s_id 学号
     * */
    public void checkInStu (String BedName, String s_id) throws SQLException {
        String sql = "insert into Studentbed(s_id, bed_id)\n" +
                "select ?, bed_id\n" +
                "from BedNameTrans\n" +
                "where BedName = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, s_id);
        preparedStatement.setString(2, BedName);
        try {
            preparedStatement.execute();
            conn.commit();
        }catch (SQLException e){
            conn.rollback();
            throw e;
        } finally {
            preparedStatement.close();
        }
    }
    /**
     * 学生退宿
     * @param s_id 学号
     * */
    public void checkOutStu (String s_id) throws SQLException {
        String sql = "delete from Studentbed\n" +
                "where s_id = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, s_id);
        try {
            preparedStatement.execute();
            conn.commit();
        }catch (SQLException e){
            conn.rollback();
            throw e;
        } finally {
            preparedStatement.close();
        }
    }

    /**
     * 获取已入住学生的信息
     * @return 所有学生的信息
     * */
    public List<StuData> getCheckInStuData() throws SQLException {
        String sql = "select Student.*, BedName " +
                "from Student left join Studentbed on Student.s_id = Studentbed.s_id " +
                "left join BedNameTrans on Studentbed.bed_id = BedNameTrans.bed_id " +
                "where BedName is not null;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        return queryStuData(preparedStatement);
    }


}
